/***********************************************************************************************************************************
 * Name:	ReadLogFile.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Read the SQL Server or SQL Agent log file(s).
 */
IF OBJECT_ID(N'TempDB..##ErrorLog') IS NOT NULL DROP TABLE ##ErrorLog;

CREATE TABLE ##ErrorLog (LogFileIndex TINYINT Null, LogDate DATETIME NOT Null, ProcessInfo NVARCHAR(128) NOT Null, LogText NVARCHAR(MAX) NOT Null);
--sys.xp_enumerrorlogs
DECLARE @DirTree TABLE (Name NVARCHAR(260) NOT Null, Depth SMALLINT NOT Null, IsFile BIT NOT Null);
DECLARE
  @ErrorLogFileName	NVARCHAR(260),
  @ErrorDumpDir		NVARCHAR(260),
  @Filter			NVARCHAR(260),
  @LogFileIndex		TINYINT;

SET NOCOUNT ON;
SET @ErrorLogFileName= CAST(SERVERPROPERTY(N'ErrorLogFileName') AS NVARCHAR(260));
SET @ErrorDumpDir= SUBSTRING(@ErrorLogFileName, 1, LEN(@ErrorLogFileName) - CHARINDEX(N'\', REVERSE(@ErrorLogFileName))) + N'\';
SET @Filter= RIGHT(@ErrorLogFileName, LEN(@ErrorLogFileName)-LEN(@ErrorDumpDir))+N'%';
INSERT INTO @DirTree EXECUTE master.sys.xp_dirtree @ErrorDumpDir, 1, 1;

DECLARE LogFileIndex CURSOR LOCAL FAST_FORWARD FOR
	WITH p (Name, Chars) AS (
		SELECT Name, CASE CHARINDEX(N'.', REVERSE(Name)) WHEN 0 THEN 0 ELSE CHARINDEX(N'.', REVERSE(Name))-1 END
		FROM @DirTree
		WHERE Name LIKE @Filter)
	  SELECT LogFileIndex= CAST(RIGHT(Name, Chars) AS TINYINT) FROM p;
OPEN LogFileIndex;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM LogFileIndex INTO @LogFileIndex;
  IF @@FETCH_STATUS <> 0 BREAK;
  INSERT INTO ##ErrorLog (LogDate, ProcessInfo, LogText) EXECUTE sys.xp_readerrorlog @LogFileIndex;
  UPDATE ##ErrorLog SET LogFileIndex= @LogFileIndex WHERE LogFileIndex IS Null;
END;
CLOSE LogFileIndex;
DEALLOCATE LogFileIndex;
CREATE INDEX CX_ErrorLog_LogDate ON ##ErrorLog (LogDate,ProcessInfo);

SET NOCOUNT OFF;
SELECT * FROM ##ErrorLog ORDER BY LogDate, ProcessInfo;

/*
	SELECT *, hh= DATEDIFF(hh,GETDATE(),LogDate) FROM ##ErrorLog WHERE LogText = N'Starting up database ''master''.' ORDER BY LogDate DESC;
	SELECT * FROM ##ErrorLog WHERE LogText LIKE '% certificate \[Cert Hash(sha1)%' ESCAPE '\' ORDER BY LogDate DESC;
**/
GO